package cis350.pcr;

import java.io.File;
import java.util.ArrayList;
import java.util.Random;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import cis350.pcr.types.Type.SearchType;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.DatabaseUtils.InsertHelper;

public class CodeLookupDB extends SQLiteOpenHelper {

	static final String dbName = "Code";

	static final String courseHistoryTable = "CourseHistoryTable";
	static final String profTable = "ProfTable";
	static final String deptTable = "DeptTable";

	static final String ID_COL = "_id";
	static final String CODE_COL = "code";
	static final String NAME_COL = "name";
	static final String KEYWORDS_COL = "keywords";
	static final String DEPTS_COL = "depts";

	public CodeLookupDB(Context context) {
		super(context, dbName, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// should have primary keys
		db.execSQL("CREATE TABLE " + courseHistoryTable + " (_id TEXT not null, code TEXT not null, name TEXT not null, keywords TEXT not null, PRIMARY KEY (name, code, _id))");
		db.execSQL("CREATE TABLE " + profTable + " (_id TEXT not null PRIMARY KEY, name TEXT not null, depts TEXT not null)");
		db.execSQL("CREATE TABLE " + deptTable + " (_id TEXT not null PRIMARY KEY, name TEXT not null)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + courseHistoryTable);
		db.execSQL("DROP TABLE IF EXISTS " + profTable);
		db.execSQL("DROP TABLE IF EXISTS " + deptTable);
	}

	public void deleteDatabase(Context context) {
		context.deleteDatabase(dbName);
	}

	public ArrayList<String> addCourseHistory(String JSON, String ID) {
		ArrayList<String> result = new ArrayList<String>();

		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransactionNonExclusive();
		InsertHelper ih = new InsertHelper(db, courseHistoryTable);

		final int idColumn = ih.getColumnIndex("_id");
		final int codeColumn = ih.getColumnIndex("code");
		final int nameColumn = ih.getColumnIndex("name");
		final int keywordsColumn = ih.getColumnIndex("keywords");

		try {
			JSONObject dept = new JSONObject(JSON);
			JSONArray courses = dept.getJSONObject("result").getJSONArray("coursehistories");

			JSONObject entry;
			JSONArray aliases;
			String name;
			String code;
			String keywords;
			String[] split;
			int id;

			try {
				for (int i = 0; i < courses.length(); i++) {
					entry = courses.getJSONObject(i);

					name = entry.getString("name");
					id = entry.getInt("id");
					result.add(id+"");

					aliases = entry.getJSONArray("aliases");
					for (int j = 0; j < aliases.length(); j++) {
						code = aliases.getString(j);
						split = code.split("-");

						if (ID.equals(split[0])) {
							keywords =  code + " " + split[0] + split[1];
							code = split[0] + " " + split[1];		//code of format cis 120

							ih.prepareForInsert();

							ih.bind(idColumn, id);
							ih.bind(codeColumn, code);
							ih.bind(nameColumn, name);
							ih.bind(keywordsColumn, keywords);

							ih.execute();

							break;
						}

					}
				}
			} catch (SQLiteConstraintException e) {
				e.printStackTrace();
			}

		} catch (JSONException e) {
			e.printStackTrace();
		}

		db.setTransactionSuccessful();
		db.endTransaction();
		// db.close();

		return result;
	}


	public void addInstructors(String JSON) {
		SQLiteDatabase db = this.getWritableDatabase();

		InsertHelper ih = new InsertHelper(db, profTable);

		final int idColumn = ih.getColumnIndex("_id");
		final int nameColumn = ih.getColumnIndex("name");
		final int deptsColumn = ih.getColumnIndex("depts");

		db.beginTransactionNonExclusive();

		try {
			JSONObject toplevel = new JSONObject(JSON);
			JSONArray instructors = toplevel.getJSONObject("result").getJSONArray("values");

			JSONObject entry;
			JSONArray dept;
			StringBuilder depts = new StringBuilder();
			String name;
			String id;

			try {
				for (int i = 0; i < instructors.length(); i++) {
					entry = instructors.getJSONObject(i);

					name = entry.getString("name");
					id = entry.getString("id");

					dept = entry.getJSONArray("depts");
					depts.delete(0, depts.length());

					for (int j = 0; j < dept.length(); j++)
						depts.append(dept.getString(j) + ", ");

					depts.delete(depts.length()-2, depts.length());

					// if (i % 500 == 0)
					// Log.d("Prof", name + " " + i + "/" +
					// instructors.length());

					ih.prepareForInsert();

					ih.bind(idColumn, id);
					ih.bind(nameColumn, name);
					ih.bind(deptsColumn, depts.toString());

					ih.execute();
				}
			} catch (SQLiteConstraintException e) {
				e.printStackTrace();
			}

		} catch (JSONException e) {
			e.printStackTrace();
		}

		db.setTransactionSuccessful();
		db.endTransaction();
		// db.close();
	}

	public void addDepartments(String JSON) {
		SQLiteDatabase db = this.getWritableDatabase();

		InsertHelper ih = new InsertHelper(db, deptTable);

		final int idColumn = ih.getColumnIndex("_id");
		final int nameColumn = ih.getColumnIndex("name");

		db.beginTransactionNonExclusive();

		try {
			JSONObject toplevel = new JSONObject(JSON);
			JSONArray depts = toplevel.getJSONObject("result").getJSONArray("values");

			try {
				for (int i = 0; i < depts.length(); i++) {
					JSONObject entry = depts.getJSONObject(i);

					String id = entry.getString("id");
					String name = entry.getString("name");

					ih.prepareForInsert();

					ih.bind(idColumn, id);
					ih.bind(nameColumn, name);

					ih.execute();
				}
			} catch (SQLiteConstraintException e) {
				e.printStackTrace();
			}

		} catch (JSONException e) {
			e.printStackTrace();
		}

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	// return arraylist in the format "id,code,coursename",...
	public ArrayList<String> getCourse(String input) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cur = db.rawQuery("Select * from " + courseHistoryTable + " where name like '%" + input + "%' or code like '%"
				+ input + "%' or keywords like '%" + input + "%' order by code limit 3", new String[] {});

		ArrayList<String> result = new ArrayList<String>();

		while (cur.moveToNext()) {
			String entry = cur.getString(0) + "," + cur.getString(1) + "," + cur.getString(2);
			result.add(entry);
		}

		cur.close();
		db.close();
		return result;
	}

	// return arraylist in the format "id,name,dept1 dept 2",...
	public ArrayList<String> getProf(String input) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + profTable + " where name like '%" + input
				+ "%' order by name limit 3", new String[] {});

		ArrayList<String> result = new ArrayList<String>();

		while (cur.moveToNext()) {
			String entry = cur.getString(0) + "," + cur.getString(1) + "," + cur.getString(2);
			result.add(entry);
		}

		cur.close();
		db.close();
		return result;
	}

	// return arraylist in the format "id,code,coursename",...
	public Cursor getSearchCursor(String input, int limDepts, int limCourses, int limProfs) {
		SQLiteDatabase db = this.getReadableDatabase();

		// SQLite is NOT MySQL - can't do (Select...) U (Select..)

		Cursor cur;
		cur = db.rawQuery("select _id from "+ deptTable+ " where _id like '"+input+ "%' or _id like '"+input.trim()+ "'", null);		//check if input is prefix of a dept

		if (cur.getCount() == 0)
			cur = db.rawQuery("Select _id, _id as title, name as subtitle, "+SearchType.DEPT.ordinal()+" as type from " +
					"(select * from " + deptTable + " where _id like '%" + input + "%' or name like '%" + input + "%' order by _id limit " + limDepts + ")"
					+ "UNION Select _id, code as title, name as subtitle, "+SearchType.COURSE.ordinal()+" as type from " +
					"(select * from " + courseHistoryTable + " where name like '%" + input + "%' or code like '%" + input + "%' or keywords like '%" + input + "%' order by code limit " + limCourses + ")"
					+ "UNION Select _id, name as title, depts as subtitle, "+SearchType.INSTRUCTOR.ordinal()+" as type from " +
					"(select * from "+ profTable + " where name like '%" + input + "%' order by name limit " + limProfs
					+ ") order by type, title", null);
		else
			cur = db.rawQuery("Select _id, _id as title, name as subtitle, "+SearchType.DEPT.ordinal()+" as type from " +
					"(select * from " + deptTable + " where _id like '%" + input + "%' order by _id limit " + limDepts + ")"
					+ "UNION Select _id, code as title, name as subtitle, "+SearchType.COURSE.ordinal()+" as type from " +
					"(select * from " + courseHistoryTable + " where code like '" + input.trim() + "%' order by code limit " + limCourses + ")"
					+ "UNION Select _id, name as title, depts as subtitle, "+SearchType.INSTRUCTOR.ordinal()+" as type from " +
					"(select * from "+ profTable + " where name like '%" + input + "%' order by name limit " + limProfs
					+ ") order by type, title", null);

		// db.close();
		//cur.moveToPosition(-1);
		return cur;
	}


	public ArrayList<String> getAllCourses() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + courseHistoryTable, new String[] {});

		ArrayList<String> result = new ArrayList<String>();

		while (cur.moveToNext()) {
			String entry = cur.getString(0) + "," + cur.getString(1) + "," + cur.getString(2);
			result.add(entry);
		}

		cur.close();
		db.close();
		return result;
	}

	public void getAllProfs(ArrayList<String> path, ArrayList<String> term, ArrayList<String> name) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + profTable + " order by name", null);

		while (cur.moveToNext()) {
			if(cur.getString(0).trim().length()>0&&cur.getString(1).trim().length()>0){ 
				path.add(cur.getString(0));
				term.add(cur.getString(1));
				name.add(cur.getString(1));
			}
		}

		cur.close();
		db.close();
	}

	public void getAllDepts(ArrayList<String> path, ArrayList<String> term, ArrayList<String> name) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + deptTable + " order by _id", null);

		while (cur.moveToNext()) {
			path.add(cur.getString(0));
			term.add(cur.getString(0));
			String space = " - ";
			name.add(cur.getString(0) + space + cur.getString(1));

		}
		cur.close();
		db.close();
	}


	public void getCourseinDept(String department, ArrayList<String> path) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select * from " + courseHistoryTable + " where code like '" + department + " %' order by code", null);


		while (cur.moveToNext()) {
			path.add(cur.getString(cur.getColumnIndex("_id")));
		}

		cur.close();
		db.close();
	}

	public SearchType getRandom(String[] result) {
		SearchType type = null;

		int numDepts = getNumDepts();
		int numCourses = getNumCourses();
		int numProfs = getNumProfs();


		Random random = new Random(System.nanoTime());
		int rand = random.nextInt(numCourses+numProfs+numDepts);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur;

		//departments
		if (rand < numDepts) {
			type = SearchType.DEPT;

			cur = db.rawQuery("select _id, _id as title from " + deptTable + " where rowid = " + rand, null);

			if (cur.moveToNext())
				result[0] = cur.getString(cur.getColumnIndex("_id"));
		}
		//courses
		else if (rand < numCourses + numDepts) {
			type = SearchType.COURSE;

			cur = db.rawQuery("select _id, code as title from " + courseHistoryTable + " where rowid = " + (rand-numDepts), null);

			if (cur.moveToNext())
				result[0] = "coursehistories/" + cur.getString(cur.getColumnIndex("_id")) + "/reviews";
		}
		//profs
		else {
			type = SearchType.INSTRUCTOR;

			cur = db.rawQuery("select _id, name as title from " + profTable + " where rowid = " + (rand-numDepts-numCourses), null);

			if (cur.moveToNext())
				result[0] = "instructors/" + cur.getString(cur.getColumnIndex("_id")) + "/reviews";
		}

		if (result[0] != null)
			result[1] = cur.getString(cur.getColumnIndex("title"));

		cur.close();
		db.close();
		return type;
	}

	public int getNumCourses() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select count(*) from " + courseHistoryTable, new String[] {});

		int result = 0;
		if (cur.moveToFirst() && !cur.isNull(0))
			result = cur.getInt(0);
		cur.close();
		db.close();

		return result;
	}

	public int getNumProfs() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select count(*) from " + profTable, new String[] {});

		int result = 0;
		if (cur.moveToFirst() && !cur.isNull(0))
			result = cur.getInt(0);
		cur.close();
		db.close();

		return result;
	}

	public int getNumDepts() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("Select count(*) from " + deptTable, new String[] {});

		int result = 0;
		if (cur.moveToFirst() && !cur.isNull(0))
			result = cur.getInt(0);

		cur.close();
		db.close();

		return result;
	}

	public long getDatabaseSize(Context context) {
		File f = context.getDatabasePath(dbName);
		long dbSize = f.length();
		return dbSize;
	}
}
